package com.traning.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.traning.dto.Employee;

public class EmployeeDAO {

	public Employee validateEmployeeUpdate(String id, String username,
			String password, String dob, String address, String phone) {

		Employee employee = null;
		String sqlquery = "UPDATE employeelogin SET empusername=? and emppassword=? and empdob=? and empaddress=? and empphone=? WHERE empid=?";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/employee", "root", "root");

			PreparedStatement preparedStatement = con
					.prepareStatement(sqlquery);
			preparedStatement.setString(1, username);
			preparedStatement.setString(2, password);
			preparedStatement.setString(3, dob);
			preparedStatement.setString(4, address);
			preparedStatement.setString(5, phone);
			preparedStatement.setString(6, id);

			int i = preparedStatement.executeUpdate(sqlquery);
			if (i > 0) {
				System.out.println("No of rows Updated ---" + i);
			} else {
				System.out.println("No rows Updated-----" + i);
			}

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return employee;
	}

	public Employee employeesearch(String id) {

		Employee employee = null;
		String search_query = "select * from employeelogin where empid=?";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/employee", "root", "root");
			PreparedStatement preparedStatement = con
					.prepareStatement(search_query);
			preparedStatement.setString(1, id);
			ResultSet resultset = preparedStatement.executeQuery();
			while (resultset.next()) {
				employee = new Employee();
				employee.setId(resultset.getString(1));
				employee.setUsername(resultset.getString(2));
				employee.setPassword(resultset.getString(3));
				employee.setDob(resultset.getString(4));
				employee.setAddress(resultset.getString(5));
				employee.setPhone(resultset.getString(6));
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return employee;

	}

	public Employee validateEmployeeRegistration(Employee employee) {

		String sqlquery = " INSERT INTO employeelogin VALUES ('"
				+ employee.getId() + "','" + employee.getUsername() + "','"
				+ employee.getPassword() + "','" + employee.getDob() + "','"
				+ employee.getAddress() + "','" + employee.getPhone() + "')";
		String sqlquery1 = "SELECT * FROM employeelogin WHERE empid='"
				+ employee.getId() + "'";
		{
			try {
				Class.forName("com.mysql.jdbc.Driver");
				Connection conn = DriverManager.getConnection(
						"jdbc:mysql://localhost:3306/employee", "root", "root");
				Statement statement = conn.createStatement();
				int i = statement.executeUpdate(sqlquery);
				statement.executeQuery(sqlquery1);
				// SQLWarning sqlwarning = statement.getWarnings();
				// sqlwarnings
				if (i > 0) {
					System.out.println("Number of rows Inserted are" + i);
				} else {
					System.out.println("No rows Inserted.");
				}

			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

		return employee;
	}

	public Employee validateEmployeeLogin(String username, String password) {

		Employee employee = null;
		String sqlquery = "Select * from employeelogin where empusername='"
				+ username + "'and emppassword='" + password + "'";

		try {

			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/employee", "root", "root");
			Statement statement = conn.createStatement();
			ResultSet resultset = statement.executeQuery(sqlquery);

			while (resultset.next()) {
				employee = new Employee();
				employee.setId(resultset.getString("empid"));
				employee.setUsername(resultset.getString("empusername"));
				employee.setPassword(resultset.getString("emppassword"));
				employee.setDob(resultset.getString("empdob"));
				employee.setAddress(resultset.getString("empaddress"));
				employee.setPhone(resultset.getString("empphone"));
			}
		} catch (ClassNotFoundException e) {

			e.printStackTrace();
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return employee;
	}

	public Employee validateEmployeeDelete(String id) {
		Employee employee = null;
		String sqlquery = "DELETE FROM employeelogin WHERE empid='" + id + "'";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/employee", "root", "root");
			Statement statement = conn.createStatement();
			int i = statement.executeUpdate(sqlquery);
			if (i > 0) {
				System.out.println("Deleted rows---" + i);
			} else {
				System.out.println("No rows deleted.");
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return employee;
	}

	/**
	 * public static void main(String[] args) { Employee employee2 = new
	 * EmployeeUpdate().validateEmployeeUpdate("117", "chakri1991", "abcd",
	 * "05/04/1997","habsigudatarnaka","9985951682");
	 * System.out.println("Address is---"+employee2.getAddress());// }
	 */

}
